package beans;


import database.DatabaseConnection;

import java.io.PrintWriter;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;

import javax.el.ELContext;
import javax.el.ExpressionFactory;
import javax.el.ValueExpression;

import javax.faces.context.FacesContext;

import obj.ScheduleObj;
import obj.StudentClassesObj;


public class InfoClasses {
    private ArrayList<ScheduleObj> listaOrar = new ArrayList<ScheduleObj>();
    private ArrayList<StudentClassesObj> listaCredite = new ArrayList<StudentClassesObj>();
    private ArrayList<StudentClassesObj> listaRestante = new ArrayList<StudentClassesObj>();
    private int totalRestante = -1;
    private int totalCredite = -1;

    public InfoClasses() {
        super();
        orar();
        restante();
        credite();
    }


    public void orar() {
        String sql =
            "SELECT denumire,sala,ora, " + "  descriere,nume,tip " + "FROM materii m " + "INNER JOIN orar_materie om " +
            "ON m.id_materie = om.id_materie " + "INNER JOIN orar o " + "ON o.id_orar=om.id_orar " +
            "INNER JOIN profesori p " + "ON om.id_prof=p.id_prof " + "ORDER BY o.id_orar";
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        PrintWriter pw = null;
        try {
            con = DatabaseConnection.getConectare();
            System.out.println(con);
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {

                String denumire = rs.getString("denumire");
                String ora = rs.getString("ora");
                String descriere = rs.getString("descriere");
                String sala = rs.getString("sala");
                String nume = rs.getString("nume");
                String tip = rs.getString("tip");

                ScheduleObj orar = new ScheduleObj();
                orar.setDenumire(denumire);
                orar.setNume(nume);
                orar.setSala(sala);
                orar.setDescriere(descriere);
                orar.setTip(tip);
                orar.setOra(ora);
                listaOrar.add(orar);
            }

        } catch (ClassNotFoundException ex) {

        } catch (SQLException ex) {
            System.out.println("err sql" + ex);

        }

        finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException ex) {

            }
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException ex) {

            }
        }
    }

    public ArrayList<ScheduleObj> getListaOrar() {
        return listaOrar;
    }


    public void credite() {
        String sql =
        "Select Vers_max.versiune,descriere,  nota_examen,  m.credite "+
        "from materie_student ms  inner join materii m ON "+
        "m.id_materie=ms.id_materie,  "+
        "(select  max(Versiune) Versiune from materie_student) Vers_max "+
         " where  ms.versiune = Vers_max.Versiune "+
         " and  id_student=  "+getBean("#{formularStudent}").getId_student() +
         "and  ms.nota_examen >=5 "+   
         "ORDER BY m.credite";
     
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        PrintWriter pw = null;
        try {
            con = DatabaseConnection.getConectare();
            System.out.println(con);
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                String descriere = rs.getString("descriere");
                int nota = rs.getInt("nota_examen");
                int credite = rs.getInt("credite");

                StudentClassesObj mat = new StudentClassesObj();
                mat.setDescriere(descriere);
                mat.setNota(nota);
                mat.setCredite(credite);
                listaCredite.add(mat);
            }

        } catch (ClassNotFoundException ex) {

        } catch (SQLException ex) {
            System.out.println("err sql" + ex);

        }

        finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException ex) {

            }
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException ex) {

            }
        }
    }

    public void restante() {
        String sql =
            "Select  descriere,  nota_examen,  0 credite  from materie_student ms inner join materii m ON m.id_materie=ms.id_materie where id_student=" +getBean("#{formularStudent}").getId_student()+ " and nota_examen <5 ";
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        PrintWriter pw = null;
        try {
            con = DatabaseConnection.getConectare();
            System.out.println(con);
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                String descriere = rs.getString("descriere");
                int nota = rs.getInt("nota_examen");
                int credite = rs.getInt("credite");

                StudentClassesObj mat = new StudentClassesObj();
                mat.setDescriere(descriere);
                mat.setNota(nota);
                mat.setCredite(credite);
                listaRestante.add(mat);
            }

        } catch (ClassNotFoundException ex) {

        } catch (SQLException ex) {
            System.out.println("err sql" + ex);

        }

        finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException ex) {

            }
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException ex) {

            }
        }
    }

    public ArrayList<StudentClassesObj> getListaCredite() {
        return listaCredite;
    }

    public ArrayList<StudentClassesObj> getListaRestante() {
        return listaRestante;
    }

    public int getTotalRestante() {
        if (totalRestante == -1) {
            String sql =
                "Select  count(*) totalRestante from materie_student ms inner join materii m ON m.id_materie=ms.id_materie where id_student=" +getBean("#{formularStudent}").getId_student() + " and nota_examen <5 ";
            Connection con = null;
            Statement stmt = null;
            ResultSet rs = null;
            PrintWriter pw = null;
            try {
                con = DatabaseConnection.getConectare();
                System.out.println(con);
                stmt = con.createStatement();
                rs = stmt.executeQuery(sql);
                while (rs.next()) {
                    totalRestante = rs.getInt("totalRestante");

                }

            } catch (ClassNotFoundException ex) {

            } catch (SQLException ex) {
                System.out.println("err sql" + ex);

            }

            finally {
                try {
                    if (rs != null)
                        rs.close();
                } catch (SQLException ex) {

                }
                try {
                    if (stmt != null)
                        stmt.close();
                } catch (SQLException ex) {

                }
            }
        }
        return totalRestante;
    }

    public int getTotalCredite() {
        if (totalCredite == -1) {
            String sql =
                "Select  sum(m.credite) totalCredite from materie_student ms inner join materii m ON m.id_materie=ms.id_materie where id_student=" + getBean("#{formularStudent}").getId_student() + "and nota_examen >=5   ORDER BY credite";
            Connection con = null;
            Statement stmt = null;
            ResultSet rs = null;
            PrintWriter pw = null;
            try {
                con = DatabaseConnection.getConectare();
                System.out.println(con);
                stmt = con.createStatement();
                rs = stmt.executeQuery(sql);
                while (rs.next()) {
                    totalCredite = rs.getInt("totalCredite");
                }

            } catch (ClassNotFoundException ex) {

            } catch (SQLException ex) {
                System.out.println("err sql" + ex);

            }

            finally {
                try {
                    if (rs != null)
                        rs.close();
                } catch (SQLException ex) {

                }
                try {
                    if (stmt != null)
                        stmt.close();
                } catch (SQLException ex) {

                }
            }
        }
        return totalCredite;
    }
    
    
    private StudentFormularBB getBean(String bean ) {
           StudentFormularBB  studentBean=null;
           try{
             
               FacesContext fc = FacesContext.getCurrentInstance();
               ELContext el = fc.getELContext();
               ExpressionFactory exprFactory = fc.getApplication().getExpressionFactory();
               ValueExpression ve = exprFactory.createValueExpression(el, bean, Object.class);
               if(ve!=null){
               System.out.println("Am obtinut valoarea"+ve);
               }
               Object valueObject = ve.getValue(el);
               if(valueObject!=null){
               System.out.println("Am obtinut valoarea"+valueObject);
               }
               if (valueObject != null) {
                   studentBean = (StudentFormularBB)valueObject;
               } else {
               }
           }
           catch(Exception ex){
               System.out.println("!!!!!!!!!!!!!!!Excptia s-a produs in template"+ex);
               }
           return studentBean;

       }
}
